package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.google.gwt.dev.util.collect.HashMap;

@Repository
public class ApprovalDao {
	@Autowired	private SessionFactory sessionFactory;

	public List<Map> getlistpengiriman_approval(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            
           
            
           if (kode.equals("SAMPLING_APPROVAL")){
           	
           	sql = "{call BBTRXPENGIRIMAN.SETID_PLTU(?)}";
           	CallableStatement penerimaan;
           	penerimaan = con.prepareCall(sql);
           	penerimaan.setString(1, map.get("id_pltu").toString());
           	System.out.println("id pltu dao: " + map.get("id_pltu").toString());
           	penerimaan.execute();
           	
           	sql = "{call BBTRXPENGIRIMAN.SETNO_PENGAJUAN(?)}";
           	penerimaan = con.prepareCall(sql);
           	penerimaan.setString(1, map.get("no_pengajuan").toString());
           	System.out.println("no pengajuan dao: " + map.get("no_pengajuan").toString());
           	penerimaan.execute();
               
          }
            
            sql = "{? = call BBTRXPENGIRIMAN.GETLISTPENGIRIMAN(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> getlistRakor(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
            
            
        }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> getlistPltuByNorakor(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
        	
        	sql = "{call BBTRXPENGIRIMAN.SETNO_RAKOR(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("norakor").toString());
            call.execute();
        	
            
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
            
            
        }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> insertBbPengirimanBb(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try{
        	
            if (kode.equals("PENGAJUAN")){
            	String sql = null;
                CallableStatement call = null;
            	
            	sql = "{call BBTRXPENGIRIMAN.SETNO_JADWAL(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
                
                sql = "{call BBTRXPENGIRIMAN.SETNO_PENGAJUAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_pengajuan").toString());
                System.out.println("no pengajuan: " + map.get("no_pengajuan").toString());
                call.execute();
                
                sql = "{? = call BBTRXPENGIRIMAN.INSERTPENGIRIMANBB(?,?)}";
                call = con.prepareCall(sql);
                call.registerOutParameter(1, OracleTypes.VARCHAR);
                call.setString(2, kode);
                call.setString(3, map.get("crud").toString());
                call.execute();
                
                map.clear();
                map.put("message", call.getString(1)); 
                System.out.println("return: " + map.get("message").toString());
                
            }else if (kode.equals("SAMPLING")){
            	
            	String sql = null;
                CallableStatement callsampling = null;
            	
            	sql = "{call BBTRXPENGIRIMAN.SETASH(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("ash").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETCV(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("cv").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETHGI(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("hgi").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETTS(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("ts").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETTM(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("tm").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETID_PLTU(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("id_pltu").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETID_PEMASOK(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("id_pemasok").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETID_SURVEYOR(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("id_surveyor").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETID_PENGIRIMAN(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("id_pengiriman").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETJUMLAH_SAMPLING(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("jumlah_sampling").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETVOL_KIRIM(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("jumlah_sampling").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETVOL_TERIMA(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("jumlah_sampling").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETSTATUS(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("status").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETCREATEBY(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("user").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETAPP_ACCESS(?)}";
            	callsampling = con.prepareCall(sql);
            	callsampling.setString(1, map.get("app_access").toString());
            	callsampling.execute();
            	
            	sql = "{call BBTRXPENGIRIMAN.SETNO_PENGAJUAN(?)}";
                callsampling = con.prepareCall(sql);
                callsampling.setString(1, map.get("no_pengajuan").toString());
                System.out.println("no pengajuan: " + map.get("no_pengajuan").toString());
                callsampling.execute();
                
                sql = "{? = call BBTRXPENGIRIMAN.INSERTPENGIRIMANBB(?,?)}";
                callsampling = con.prepareCall(sql);
                callsampling.registerOutParameter(1, OracleTypes.VARCHAR);
                callsampling.setString(2, kode);
                callsampling.setString(3, map.get("crud").toString());
                callsampling.execute();
                
                map.clear();
                map.put("message", callsampling.getString(1)); 
                System.out.println("return: " + map.get("message").toString());
            	
            	
            }
            
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
		}
        lMap.add(map);
        return lMap;
	}
	
	public List<Map> insertGudangBb(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try{
        	
        		String sql = null;
                CallableStatement call = null;
                
                sql = "{call bbtrxgudang.SETNO_PERMINTAAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
                
                sql = "{call bbtrxgudang.SETSATUAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
                
                sql = "{call bbtrxgudang.SETVOLUME_PENGAJUAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
                
                sql = "{call bbtrxgudang.SETID_UNIT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
                
                sql = "{call bbtrxgudang.SETKETERANGAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
            	
            	sql = "{call bbtrxgudang.SETCREATEBY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_jadwal").toString());
                call.execute();
                
                sql = "{call bbtrxgudang.SETAPP_ACCESS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_pengajuan").toString());
                System.out.println("no pengajuan: " + map.get("no_pengajuan").toString());
                call.execute();
                
                sql = "{? = call bbtrxgudang.INSERTPENGIRIMANBB(?,?)}";
                call = con.prepareCall(sql);
                call.registerOutParameter(1, OracleTypes.VARCHAR);
                call.setString(2, kode);
                call.setString(3, map.get("crud").toString());
                call.execute();
                
                map.clear();
                map.put("message", call.getString(1)); 
                System.out.println("return: " + map.get("message").toString());
                
                sql = "{? = call BBTRXPENGIRIMAN.INSERTPENGIRIMANBB(?,?)}";
                call = con.prepareCall(sql);
                call.registerOutParameter(1, OracleTypes.VARCHAR);
                call.setString(2, kode);
                call.setString(3, map.get("crud").toString());
                call.execute();
                
                map.clear();
                map.put("message", call.getString(1)); 
                System.out.println("return: " + map.get("message").toString());
            
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
		}
        lMap.add(map);
        return lMap;
	}
	
}
